package com.mproject.service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Locale;

import com.project.connection.DatabaseConnection;
import javax.ws.rs.GET;
import javax.ws.rs.Path;
import javax.ws.rs.Produces;
import javax.ws.rs.QueryParam;

//Sets the path to base URL
@Path("/get")
public class Get {
	public static Connection con = DatabaseConnection.getConnection();

	// GET PLANS 
	@GET
	@Path("/plans")
	@Produces("text/plain")
	public String getPlans(@QueryParam("project_id") String project_id) {
		String result = "{\"Results\":[";

		try {
			Statement stmt = con.createStatement();
			String query = "select * from plan where project_id = '" +project_id+ "' order by start, end";
			ResultSet rs = stmt.executeQuery(query);

			int plan_existed = 0;
			while(rs.next()) {
				plan_existed = 1;
				result += "{\"id\":" +rs.getString("id") 
						+ ",\"title\":\"" + rs.getString("title") + "\""
						+ ",\"des\":\"" + rs.getString("des") + "\""
						+ ",\"start\":" + rs.getString("start")
						+ ",\"end\":" + rs.getString("end")
						+ ",\"incharged\":\"" + rs.getString("incharged") + "\""
						+ ",\"project_id\":\"" + rs.getString("project_id") + "\""
						+ "},";
			}

			if(plan_existed == 1) result = result.substring(0, result.length() - 1);			
		} catch (Exception e) {
			System.out.println(e);
		}

		result += "]}";
		return result;
	}

	// GET UPCOMING 7 DAYS OF MEETING AND TASKS
	@GET
	@Path("/upcoming")
	@Produces("text/plain")
	public String getUpcoming(@QueryParam("nusnet") String nusnet,
			@QueryParam("date") long date) {
		String result = "{\"Results\":[";

		Calendar c = Calendar.getInstance(Locale.getDefault());
		c.setTimeInMillis(date);

		try {
			Statement stmt = con.createStatement();
			String query = "select * from project where nusnet = '" +nusnet+ "';";
			ResultSet rs = stmt.executeQuery(query);

			int project_existed = 0;
			ArrayList<String> projects = new ArrayList<String>();

			while(rs.next()) {
				project_existed = 1;
				projects.add(rs.getString("project_id"));
			}

			for(int i = 0; i < 7; i++) {
				result += "{\"" +i+ "\":[";

				result += getWork2(projects, c.getTimeInMillis());

				result = result.substring(0, result.length() - 1);
				c.add(Calendar.DATE, 1);

				result += "]},";
			}

			if(project_existed == 1) result = result.substring(0, result.length() - 1);			
		} catch (Exception e) {
			System.out.println(e);
		}

		result += "],\"date\":" +date+ "}";
		return result;
	}

	// GET MEETINGS AND TASKS 2
	public String getWork2(ArrayList<String> projects, long date) {
		String result = "";

		try {
			Statement stmt = con.createStatement();
			String query = "select * from work where date = "+date+" order by start";
			ResultSet rs = stmt.executeQuery(query);

			int plan_existed = 0;
			while(rs.next()) {
				plan_existed = 1;
				int add = 0;

				for(int i = 0; i < projects.size(); i++) {
					if(rs.getString("project_id").equals(projects.get(i))) {
						add = 1;
						break;
					}
				}

				if(add == 1) {
					result += "{\"id\":" +rs.getString("id") 
							+ ",\"project_id\":\"" + rs.getString("project_id") + "\""
							+ ",\"project_name\":\"" + rs.getString("project_name") + "\""
							+ ",\"cat\":\"" + rs.getString("cat") + "\""
							+ ",\"start\":" + rs.getLong("start")
							+ ",\"end\":" + rs.getLong("end")
							+ ",\"due\":" + rs.getLong("due")
							+ ",\"date\":" + rs.getLong("date")
							+ ",\"title\":\"" + rs.getString("title") + "\""
							+ ",\"des\":\"" + rs.getString("des") + "\""
							+ ",\"venue\":\"" + rs.getString("venue") + "\""
							+ "},";
				}
			}

		} catch (Exception e) {
			System.out.println(e);
		}

		if(result.length() == 0)
			result = "]";
		return result;
	}

	@GET
	@Path("/work")
	@Produces("text/plain")
	public String getWork(@QueryParam("project_id") String project_id,
			@QueryParam("date") long date) {
		String result = "{\"Results\":[";

		try {
			Statement stmt = con.createStatement();
			String query = "select * from work where project_id = '" +project_id+ "' and date = "+date+" order by start";
			ResultSet rs = stmt.executeQuery(query);

			int plan_existed = 0;
			while(rs.next()) {
				plan_existed = 1;
				result += "{\"id\":" +rs.getString("id") 
						+ ",\"project_id\":\"" + rs.getString("project_id") + "\""
						+ ",\"project_name\":\"" + rs.getString("project_name") + "\""
						+ ",\"cat\":\"" + rs.getString("cat") + "\""
						+ ",\"start\":" + rs.getLong("start")
						+ ",\"end\":" + rs.getLong("end")
						+ ",\"due\":" + rs.getLong("due")
						+ ",\"date\":" + rs.getLong("date")
						+ ",\"title\":\"" + rs.getString("title") + "\""
						+ ",\"des\":\"" + rs.getString("des") + "\""
						+ ",\"venue\":\"" + rs.getString("venue") + "\""
						+ "},";
			}

			if(plan_existed == 1) result = result.substring(0, result.length() - 1);			
		} catch (Exception e) {
			System.out.println(e);
		}

		result += "]}";
		return result;
	}

	// GET NEXT MEETING AND TASK
	@GET
	@Path("/next")
	@Produces("text/plain")
	public String getLatest(@QueryParam("nusnet") String nusnet) {
		String result = "{\"Results\":[";

		try {
			Statement stmt = con.createStatement();
			String query = "select * from project where nusnet = '" +nusnet+ "'";
			ResultSet rs = stmt.executeQuery(query);

			int project_existed = 0;

			while(rs.next()) {
				project_existed = 1;

				String project_id = rs.getString("project_id");
				result += "{\"project_id\":\"" + project_id + "\"";

				result += ",\"meeting\":" + getNextMeeting(project_id);
				result += ",\"task\":" + getNextTask(project_id) + "},";
			}

			if(project_existed == 1) result = result.substring(0, result.length() - 1);			
		} catch (Exception e) {
			System.out.println(e);
		}

		result += "]}";
		return result;
	}

	private String getNextMeeting(String project_id) {
		String result = "0";

		try {
			Statement stmt = con.createStatement();
			String query = "select * from work where project_id = '" +project_id+ "' and cat = 'meeting' order by date limit 1";
			ResultSet rs = stmt.executeQuery(query);
			while(rs.next()) {
				result = "" + rs.getLong("start");
				if(result.length() == 0) result = "0";
			}
		}
		catch (Exception e) {
			System.out.println(e);
		}

		return result;
	}

	private String getNextTask(String project_id) {
		String result = "0";

		try {
			Statement stmt = con.createStatement();
			String query = "select * from work where project_id = '" +project_id+ "' and cat = 'task' order by date limit 1";
			ResultSet rs = stmt.executeQuery(query);
			while(rs.next()) {
				result = "" + rs.getLong("due");
				if(result.length() == 0) result = "0";
			}
		}
		catch (Exception e) {
			System.out.println(e);
		}

		return result;
	}


	// GET MEETINGS AND TASKS IN A SPECIFIC MONTH
	@GET
	@Path("/month_work")
	@Produces("text/plain")
	public String getMonthWork(@QueryParam("project_id") String project_id,
			@QueryParam("month") int month,
			@QueryParam("year") int year) {
		String result = "{\"Results\":[";

		try {
			Statement stmt = con.createStatement();
			String query = "select distinct date from work where project_id = '" +project_id+ "' order by start";
			ResultSet rs = stmt.executeQuery(query);

			int plan_existed = 0;
			while(rs.next()) {				
				Calendar c = Calendar.getInstance(Locale.getDefault());
				c.setTimeInMillis(rs.getLong("date"));

				int m = c.get(Calendar.MONTH);
				int y = c.get(Calendar.YEAR);
				int d = c.get(Calendar.DAY_OF_MONTH);

				if (month == m && year == y) {
					plan_existed = 1;
					result += "{\"index\":" + d
							+ "},";
				}
			}

			if(plan_existed == 1) result = result.substring(0, result.length() - 1);			
		} catch (Exception e) {
			System.out.println(e);
		}

		result += "]}";
		return result;
	}

	// GET LATEST MESSAGES
	@GET
	@Path("/latest_messages")
	@Produces("text/plain")
	public String getLatestMessages(@QueryParam("user") String user,
			@QueryParam("project_id") String project_id) {
		String result = "{\"Results\":[";

		try {
			Statement stmt = con.createStatement();
			String query = "select from_id AS members from message where to_id = '" +user+ "' and project_id = '" +project_id+ "' UNION select to_id AS members from message where from_id = '" +user+ "' and project_id = '" +project_id+ "';";
			ResultSet rs = stmt.executeQuery(query);

			ArrayList<String> members = new ArrayList<String>();
			ArrayList<Long> latest_date = new ArrayList<Long>();

			int msg_existed = 0;
			String member = "";

			while(rs.next()) {
				msg_existed = 1;
				member = rs.getString("members");
				members.add(member);
				latest_date.add(getLatestMessageDate(user, member, project_id));
			}

			long max = -1;
			int index = -1;
			while(members.size() > 0) {
				// Get the index of max date in the ArrayList
				for(int i = 0; i < members.size(); i++) {
					if(max < latest_date.get(i)) {
						max = latest_date.get(i);
						index = i;
					}
				}
				result += getPairMessage(user, members.get(index), project_id);
				members.remove(index);
				latest_date.remove(index);
				max = -1;
				index = -1;
			}

			if(msg_existed == 1) result = result.substring(0, result.length() - 1);	
		} catch (Exception e) {
			System.out.println(e);
		}
		result += "]}";
		return result;
	}

	// METHOD GETTING LATEST MESSAGE DATE
	private long getLatestMessageDate(String from, String to, String project_id) {
		long result = 0;
		String query = "select date from message where ((from_id='" +from+ "' and to_id='" +to+ "') or (from_id='" +to+ "' and to_id='" +from+ "')) and project_id='" +project_id+ "' order by date desc limit 1;";

		try{
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			while(rs.next()) {
				result += rs.getLong("date");
			}
		} catch (Exception e) {
			System.out.println(e);
		}
		return result;
	}

	// METHOD GETTING LATEST PAIR MESSAGE
	private String getPairMessage (String user, String member, String project_id) {
		String result = "";
		String query = "select message, date from message where ((from_id='" +user+ "' and to_id='" +member+ "') or (from_id='" +member+ "' and to_id='" +user+ "')) and project_id='" +project_id+ "' order by date desc limit 1;";

		try{
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			while(rs.next()) {
				result += "{\"id\":\"" + member + "\""
						+ ",\"name\":\"" + getName(member) + "\""
						+ ",\"message\":\"" + rs.getString("message") + "\""
						+ ",\"date\":" + rs.getLong("date")	
						+ "},";
			}			
		} catch (Exception e) {
			System.out.println(e);
		}
		return result;
	}

	// METHOD GET NAME FROM NUSNET ID
	private String getName(String member) {
		String result = "";
		String query = "select name from user where nusnet = '" +member+ "'";

		try{
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			while(rs.next()) {
				result += rs.getString("name");
			}
		} catch (Exception e) {
			System.out.println(e);
		}
		return result;
	}

	// GET MESSAGES
	@GET
	@Path("/messages")
	@Produces("text/plain")
	public String getMessages(@QueryParam("from") String from,
			@QueryParam("to") String to,
			@QueryParam("project_id") String project_id) {
		String result = "{\"Results\":[";

		try {
			Statement stmt = con.createStatement();
			String query = "select * from message where ((from_id = '" +from+ "' and to_id = '" +to+ "') or (from_id = '" +to+ "' and to_id = '" +from+ "')) and project_id = '" +project_id+ "' order by date";
			ResultSet rs = stmt.executeQuery(query);

			int chat_existed = 0;
			while(rs.next()) {
				chat_existed = 1;
				String flag = "false";
				if (rs.getString("from_id").equals(from))
					flag = "true";
				result += "{\"isSend\":" +flag + ",\"message\":\"" + rs.getString("message") +"\"},";
			}

			if(chat_existed == 1) result = result.substring(0, result.length() - 1);			
		} catch (Exception e) {
			System.out.println(e);
		}

		result += "]}";
		return result;
	}

	// GET BUSY HOURS
	@GET
	@Path("/busy")
	@Produces("text/plain")
	public String getBusyHours(@QueryParam("project_id") String project_id,
			@QueryParam("date") long date) {
		String result = "{\"Results\":[";

		try {
			Statement stmt = con.createStatement();
			String query = "select * from busy where project_id = '" +project_id+ "' and date = '" +date+ "' order by date";
			ResultSet rs = stmt.executeQuery(query);

			int plan_existed = 0;
			while(rs.next()) {
				plan_existed = 1;
				result += "{\"id\":" +rs.getString("id") 
						+ ",\"date\":" + rs.getString("date")
						+ ",\"block\":" + rs.getString("block")					
						+ ",\"nusnet\":\"" + rs.getString("nusnet") + "\""
						+ ",\"name\":\"" + getName(rs.getString("nusnet")) + "\""
						+ "},";
			}

			if(plan_existed == 1) result = result.substring(0, result.length() - 1);			
		} catch (Exception e) {
			System.out.println(e);
		}

		result += "]}";
		return result;
	}
}